﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;

namespace DBConstruct
{
    /// <summary>
    ///SQLDBAccessor 的摘要说明
    /// </summary>
    public sealed class SQLDatabase : DatabaseFactory
    {
        //私有字段，连接和命令
        private SqlConnection _connection = null;
        private SqlCommand _cmd = null;

        /// <summary>
        /// 无实现构造函数
        /// </summary>
        public SQLDatabase()
        {
            this._connection = new SqlConnection();
        }

        /// <summary>
        /// 根据传入的不可修改性连接字符串来实例化一个Connection对象
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        public SQLDatabase(string connString)
        {
            this._connection = new SqlConnection(connString);
        }

        /// <summary>
        /// 打开连接
        /// </summary>
        public override void OpenConnection()
        {
            if (this._connection != null && this._connection.State == ConnectionState.Closed)
            {
                this._connection.Open();
            }
        }

        /// <summary>
        /// 关闭连接
        /// </summary>
        public override void CloseConnection()
        {
            if (this._connection != null && this._connection.State == ConnectionState.Open)
                this._connection.Close();
        }

        /// <summary>
        /// 清除命令中的参数组
        /// </summary>
        public override void ClearParameters()
        {
            if (this._cmd != null && this._cmd.Parameters != null)
            {
                this._cmd.Parameters.Clear();
            }
        }

        /// <summary>
        /// 清除命令
        /// </summary>
        public override void DisposeCommand()
        {
            if (this._cmd != null)
            {
                this._cmd.Dispose();
            }
        }

        /// <summary>
        /// 对数据库进行查询，将得到信息通过DataSet存储在内存中返回，有命令参数组
        /// </summary>
        /// <param name="commandString">命令字符串</param>
        /// <param name="dbParameter">参数组</param>
        /// <returns>DataSet容器数据集</returns>
        public override DataSet ExcuteReader(string commandString, object[] dbParameter)
        {
            try
            {
                //实例一个命令并设定其连接
                this._cmd = new SqlCommand(commandString);
                this._cmd.Connection = this._connection;
                if (dbParameter != null)
                {
                    //转换输入的参数组
                    SqlParameter[] parameters = (SqlParameter[])dbParameter;
                    //往命令中添加参数
                    foreach (SqlParameter sqlParam in parameters)
                    {
                        this._cmd.Parameters.Add(sqlParam);
                    }
                }
                //将数据输出到DataSet
                DataSet ds = new DataSet();
                using (SqlDataAdapter da = new SqlDataAdapter())
                {
                    da.SelectCommand = _cmd;
                    da.Fill(ds);
                }
                return ds;
            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
                //清除所有参数，并释放command占用的资源，关闭连接
                ClearParameters();
                DisposeCommand();
                CloseConnection();
            }
        }

        /// <summary>
        /// 对数据库检索，查找是否存在指定值，有命令参数组
        /// </summary>
        /// <param name="commandString">命令字符串</param>
        /// <param name="dbParameter">参数组</param>
        /// <returns>布尔值</returns>
        public override bool IsExist(string commandString, object[] dbParameter)
        {
            try
            {
                //实例一个命令并设定其连接
                this._cmd = new SqlCommand(commandString);
                this._cmd.Connection = this._connection;
                if (dbParameter != null)
                {

                    //转换输入的参数组
                    SqlParameter[] parameters = (SqlParameter[])dbParameter;
                    //往命令中添加参数
                    foreach (SqlParameter sqlParam in parameters)
                    {
                        this._cmd.Parameters.Add(sqlParam);
                    }
                }
                OpenConnection();
                using (SqlDataReader dr = this._cmd.ExecuteReader())
                {
                    if (dr.Read())
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
            }
            catch (Exception ex)
            {
                return false;
            }
            finally
            {
                ClearParameters();
                DisposeCommand();
                CloseConnection();
            }
        }

        /// <summary>
        /// 根据输入的命令和参数组对数据库进行无查询操作，包括插入、更新、删除及存储过程等，返回影响的行数
        /// </summary>
        /// <param name="commandString">命令字符串</param>
        /// <param name="dbParameter">参数组</param>
        /// <returns>int类型数据，表示影响的行数</returns>
        public override int ExcuteNonQuery(string commandString, object[] dbParameter)
        {
            try
            {
                //实例一个命令并设定其连接
                this._cmd = new SqlCommand(commandString);
                this._cmd.Connection = this._connection;
                if (dbParameter != null)
                {
                    //转换输入的参数组
                    SqlParameter[] parameters = (SqlParameter[])dbParameter;
                    //往命令中添加参数
                    foreach (SqlParameter sqlParam in parameters)
                    {
                        this._cmd.Parameters.Add(sqlParam);
                    }
                }
                OpenConnection();
                return this._cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                return -1;
            }
            finally
            {
                ClearParameters();
                CloseConnection();
            }
        }

        /// <summary>
        /// 获取当前连接字符串
        /// </summary>
        /// <returns>连接字符串</returns>
        public override string GetConnString()
        {
            return this._connection.ConnectionString;
        }

        /// <summary>
        /// 设定当前连接字符串
        /// </summary>
        /// <param name="connString">传入的连接字符串</param>
        public override void SetConnString(string connString)
        {
            this._connection.ConnectionString = connString;
        }
    }
}
